package models

import (
	"strings"
	"time"

	"github.com/astaxie/beego/orm"
	"strconv"
)

type LogRead struct {
	Id           int       `orm:"column(id);auto"`
	UserId       string    `orm:"column(user_id);size(8)" description:"用户ID"`
	AppId        int       `orm:"column(app_id)" description:"应用ID"`
	BookId       int       `orm:"column(book_id)" description:"书本ID"`
	ChapterId    int       `orm:"column(chapter_id)" description:"章节ID"`
	BookChannel  int8      `orm:"column(book_channel)" description:"频道类型"`
	BookCategory int       `orm:"column(book_category)" description:"书类别"`
	ReadDate     time.Time `orm:"column(read_date);type(datetime)" description:"阅读日期"`
}

type CatAndCount struct {
	BookCategory int
	Cnt          int
}

type ChannelAndCount struct {
	BookChannel int8
	Cnt         int
}

func (t *LogRead) TableName() string {
	return "log_read"
}

func init() {
	orm.RegisterModel(new(LogRead))
}

// 平均点击数（点击数/章节总数）
func GetAveClicks() (map[string]interface{}) {
	o := orm.NewOrm()
	// 1.book_id
	bookId := make([]int, 0)
	o.Raw("select DISTINCT(book_id) from log_read").QueryRows(&bookId)

	// 2.book
	in := make([]string, 0)
	for _, id := range bookId {
		in = append(in, strconv.Itoa(id))
	}
	bookList := make([]*Book, 0)
	o.Raw("select id,chapter_total,category,channel_type from book where id in(" + strings.Join(in, ",") + ")").QueryRows(&bookList)

	// 3.cat and count
	catAndCounts := make([]*CatAndCount, 0)
	catAndAveClicks := make(map[int]int)
	o.Raw("select book_category, count(id) as cnt from log_read group by book_category order by book_category").QueryRows(&catAndCounts)

	for _, catAndCount := range catAndCounts {
		// 计算章节总数
		total := 0
		clicks := 0
		for _, book := range bookList {
			if book.Category == catAndCount.BookCategory {
				total += book.ChapterTotal
			}
		}
		// 平均点击数
		clicks += catAndCount.Cnt
		avgClicks := int(float64(clicks) / float64(total) * 100)
		catAndAveClicks[catAndCount.BookCategory] = avgClicks
	}

	catName := make([]string, 0)
	catClick := make([]float64, 0)
	for _, cat := range catList {
		catName = append(catName, cat.CategoryFullname)
		if catAndAveClick, find := catAndAveClicks[cat.Id]; find {
			catClick = append(catClick, float64(catAndAveClick)/100)
		} else {
			catClick = append(catClick, 0)
		}
	}
	// 4.channel and count
	channelAndCounts := make([]*ChannelAndCount, 0)
	channelAndAveClicks := make(map[int8]int)
	o.Raw("select book_channel, count(id) as cnt from log_read group by book_channel order by book_channel").QueryRows(&channelAndCounts)

	for _, channelAndCount := range channelAndCounts {
		// 计算频道书本总数
		total := 0
		clicks := 0
		for _, book := range bookList {
			if book.ChannelType == channelAndCount.BookChannel {
				total ++
			}
		}
		// 平均点击数
		clicks += channelAndCount.Cnt
		avgClicks := int(float64(clicks) / float64(total) * 100)
		channelAndAveClicks[channelAndCount.BookChannel] = avgClicks
	}

	channelName := []string{"综合", "男频", "女频"}
	channelClicks := make([]map[string]interface{}, 0)
	for k, v := range channelName {
		if k == 0 {
			continue
		}
		if channelAndAveClick, find := channelAndAveClicks[int8(k)]; find {
			channelClick := map[string]interface{}{
				"value": float64(channelAndAveClick) / 100,
				"name":  v,
			}
			channelClicks = append(channelClicks, channelClick)
		} else {
			channelClick := map[string]interface{}{
				"value": 0,
				"name":  v,
			}
			channelClicks = append(channelClicks, channelClick)
		}
	}

	result := map[string]interface{}{
		"name":      "平均点击数",
		"xData":     catName,
		"yData":     catClick,
		"pieLegend": channelName,
		"pieData":   channelClicks,
	}
	return result
}

// 平均用户数（用户数/书籍总数）
func GetAveUsers() (map[string]interface{}) {
	o := orm.NewOrm()
	// 1.book_id
	bookId := make([]int, 0)
	o.Raw("select DISTINCT(book_id) from log_read").QueryRows(&bookId)

	// 2.book
	in := make([]string, 0)
	for _, id := range bookId {
		in = append(in, strconv.Itoa(id))
	}
	bookList := make([]*Book, 0)

	o.Raw("select id,chapter_total,category,channel_type from book where id in(" + strings.Join(in, ",") + ")").QueryRows(&bookList)

	// 3.cat and users
	catAndUsers := make([]*CatAndCount, 0)
	catAndAveUsers := make(map[int]int)
	//o.Raw("select book_category,count(DISTINCT(user_id)) as cnt from log_read group by book_category order by book_category").QueryRows(&catAndUsers)
	o.Raw(`select book_category, count(user_id) as cnt
from
(
 select  book_category,user_id
 from   log_read
 group by book_category,user_id
) a
group by book_category
order by book_category`).QueryRows(&catAndUsers)

	for _, catAndUser := range catAndUsers {
		// 计算书籍总数
		total := 0
		userCount := 0
		aveUser := 0
		for _, book := range bookList {
			if book.Category == catAndUser.BookCategory {
				total++
			}
		}
		// 平均用户数
		userCount += catAndUser.Cnt
		aveUser = int(float64(userCount) / float64(total) * 100)
		catAndAveUsers[catAndUser.BookCategory] = aveUser
	}

	catName := make([]string, 0)
	catUsers := make([]float64, 0)
	for _, cat := range catList {
		catName = append(catName, cat.CategoryFullname)
		if catAndAveUser, find := catAndAveUsers[cat.Id]; find {
			catUsers = append(catUsers, float64(catAndAveUser)/100)
		} else {
			catUsers = append(catUsers, 0)
		}
	}

	// 4.channel and user
	channelAndUsers := make([]*ChannelAndCount, 0)
	channelAndAveUsers := make(map[int8]int)
	//o.Raw("select book_channel, count(DISTINCT(user_id)) as cnt from log_read group by book_channel order by book_channel").QueryRows(&channelAndUsers)
	o.Raw(`select book_channel, count(user_id) as cnt
from
(
 select  book_channel,user_id
 from   log_read
 group by book_channel,user_id
) a
group by book_channel
order by book_channel
`).QueryRows(&channelAndUsers)

	for _, channelAndUser := range channelAndUsers {
		// 计算频道用户总数
		total := 0
		users := 0
		for _, book := range bookList {
			if book.ChannelType == channelAndUser.BookChannel {
				total ++
			}
		}
		// 平均用户数
		users += channelAndUser.Cnt
		avgUsers := int(float64(users) / float64(total) * 100)
		channelAndAveUsers[channelAndUser.BookChannel] = avgUsers
	}

	channelName := []string{"综合", "男频", "女频"}
	channelUsers := make([]map[string]interface{}, 0)
	for k, v := range channelName {
		if k == 0 {
			continue
		}
		if channelAndAveUser, find := channelAndAveUsers[int8(k)]; find {
			channelClick := map[string]interface{}{
				"value": float64(channelAndAveUser) / 100,
				"name":  v,
			}
			channelUsers = append(channelUsers, channelClick)
		} else {
			channelClick := map[string]interface{}{
				"value": 0,
				"name":  v,
			}
			channelUsers = append(channelUsers, channelClick)
		}
	}

	result := map[string]interface{}{
		"name":      "平均用户数",
		"xData":     catName,
		"yData":     catUsers,
		"pieLegend": channelName,
		"pieData":   channelUsers,
	}
	return result
}
